﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.SQLite;
using System.Data;
using System.Web.Configuration;

public class AlbumStorage
{
    private string _connectionString = HttpContext.Current.Server.MapPath(WebConfigurationManager.ConnectionStrings["photoru"].ConnectionString);
    private SQLiteConnection OpenСonnection()
    {
        SQLiteConnection connection = new SQLiteConnection("Data Source=" + _connectionString);
        connection.Open();
        return connection;
    }
    public DataTable GetAllAlbumsTable()
    {
        DataTable dtAllAlbumsTable = new DataTable();

        using (var connection = OpenСonnection())
        {
            string cmdSelect = "SELECT * FROM [Albums]";
            SQLiteCommand command = connection.CreateCommand();
            command.CommandText = cmdSelect;
            using (SQLiteDataReader dataRead = command.ExecuteReader())
            {
                dtAllAlbumsTable.Load(dataRead);
            }
        }
        return dtAllAlbumsTable;
    }

    public DataRow GetAlbumRowById(int id)
    {
        DataTable dtAllAlbumsTable = new DataTable();
        DataRow drAlbumRow;
        using (var connection = OpenСonnection())
        {
            string cmdSelect = "SELECT * FROM [Albums] WHERE [ID] = @id";
            SQLiteCommand command = connection.CreateCommand();
            command.Parameters.AddWithValue("@id", id);
            command.CommandText = cmdSelect;
            using (SQLiteDataReader dataRead = command.ExecuteReader())
            {
                dtAllAlbumsTable.Load(dataRead);
            }
            drAlbumRow = dtAllAlbumsTable.Rows[0];
        }
        return drAlbumRow;
    }

    public int CreateAlbum(string albumName, string author, string folderPath, DateTime creationDate)
    {
        using (var connection = OpenСonnection())
        {

            string cmdInsert = "INSERT INTO [Albums] (AlbumName, Author, FolderPath, CreationDate) VALUES (@albumName, @author, @folderPath, @creationDate)";
            SQLiteCommand command = connection.CreateCommand();
            command.Parameters.AddWithValue("@albumName", albumName);
            command.Parameters.AddWithValue("@author", author);
            command.Parameters.AddWithValue("@folderPath", folderPath);
            command.Parameters.AddWithValue("@creationDate", creationDate);
            command.CommandText = cmdInsert;
            command.ExecuteScalar();            
        }
        return GetIDFromTable(albumName);
    }

    private int GetIDFromTable(string albumName)
    {
        int id=0;
        using (var connection = OpenСonnection())
        {
            string cmdSelect = "SELECT [ID] FROM [Albums] WHERE [AlbumName] = @albumName";
            SQLiteCommand command = connection.CreateCommand();
            command.Parameters.AddWithValue("@albumName", albumName);
            command.CommandText = cmdSelect;
            using (SQLiteDataReader dataRead = command.ExecuteReader())
            {
                while (dataRead.Read())
                {
                    id = int.Parse(dataRead["ID"].ToString());
                }
            }
        }
        return id;
    }

    public bool UpdateAlbum(int id, string newAlbumName, string newfolderPath)
    {
        using (var connection = OpenСonnection())
        {
            string cmdRename = "UPDATE [Albums] SET [AlbumName]= @newAlbumName, [FolderPath]= @newfolderPath WHERE [ID] = @id";
            SQLiteCommand command = connection.CreateCommand();
            command.Parameters.AddWithValue("@id", id);
            command.Parameters.AddWithValue("@newAlbumName", newAlbumName);
            command.Parameters.AddWithValue("@newFolderPath", newfolderPath);
            command.CommandText = cmdRename;
            return command.ExecuteNonQuery() == 1;
        }        
    }

    public bool DeleteAlbum(int id)
    {
        using (var connection = OpenСonnection())
        {
            string cmdDell = "DELETE FROM Albums WHERE ID = @id";
            SQLiteCommand command = connection.CreateCommand();
            command.Parameters.AddWithValue("@id", id);
            command.CommandText = cmdDell;
            return command.ExecuteNonQuery() == 1;
        }
    }
}